今天我們要來實作後端的多租戶架構。在 SaaS 應用中，多租戶架構的設計直接影響到系統的擴展性、安全性和效能。我們將實作 Database-per-Tenant 策略，每個租戶擁有獨立的資料庫，確保資料隔離性和客製化需求。
在實作之前，我們先了解三種主要的多租戶資料庫策略：
我們的 Database-per-Tenant 架構包含以下核心組件：
// packages/kyo-core/src/database/tenant-connection.ts
interface TenantDatabaseConnection {
tenantId: string;
connectionString: string;
pool: Pool;
isActive: boolean;
lastUsed: Date;
maxConnections: number;
}
連線管理器負責管理多個租戶資料庫的連線池，實現動態建立、重用和清理連線：
export class TenantConnectionManager {
private connections = new Map<string, TenantDatabaseConnection>();
private readonly maxIdleTime = 30 * 60 * 1000; // 30分鐘
private cleanupInterval: NodeJS.Timeout;
constructor() {
// 每5分鐘清理一次閒置連線
this.cleanupInterval = setInterval(() => {
this.cleanupIdleConnections();
}, 5 * 60 * 1000);
}
async getConnection(tenantId: string): Promise<Pool> {
let connection = this.connections.get(tenantId);
if (!connection || !connection.isActive) {
connection = await this.createConnection(tenantId);
this.connections.set(tenantId, connection);
}
connection.lastUsed = new Date();
return connection.pool;
}
private async createConnection(tenantId: string): Promise<TenantDatabaseConnection> {
const connectionString = this.buildConnectionString(tenantId);
const pool = new Pool({
connectionString,
max: 10, // 每個租戶最多10個連線
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// 測試連線
try {
const client = await pool.connect();
client.release();
} catch (error) {
await pool.end();
throw new Error(`Failed to connect to tenant database: ${tenantId}`);
}
return {
tenantId,
connectionString,
pool,
isActive: true,
lastUsed: new Date(),
maxConnections: 10,
};
}
private buildConnectionString(tenantId: string): string {
const baseUrl = process.env.DATABASE_URL || 'postgresql://localhost:5432';
return `${baseUrl}/${this.getDatabaseName(tenantId)}`;
}
private getDatabaseName(tenantId: string): string {
// 租戶資料庫命名規則：kyo_tenant_{tenantId}
return `kyo_tenant_${tenantId.replace(/-/g, '_')}`;
}
}
租戶資料庫服務負責資料庫的生命週期管理，包括建立、初始化、備份和刪除：
// packages/kyo-core/src/database/tenant-service.ts
export class TenantDatabaseService {
constructor(private connectionManager: TenantConnectionManager) {}
async createTenantDatabase(request: CreateTenantDatabaseRequest): Promise<TenantDatabaseInfo> {
const { tenantId, plan, settings } = request;
const databaseName = this.getDatabaseName(tenantId);
try {
// 1. 建立資料庫
await this.createDatabase(databaseName);
// 2. 初始化 Schema
await this.initializeSchema(tenantId, plan);
// 3. 建立預設資料
await this.seedDefaultData(tenantId, settings);
// 4. 設定備份排程
await this.setupBackupSchedule(tenantId, plan);
return {
tenantId,
databaseName,
status: 'active',
createdAt: new Date(),
plan,
connectionString: this.buildConnectionString(tenantId),
backupEnabled: plan !== 'free',
};
} catch (error) {
// 失敗時清理已建立的資源
await this.rollbackDatabase(databaseName);
throw error;
}
}
private async initializeSchema(tenantId: string, plan: string): Promise<void> {
const pool = await this.connectionManager.getConnection(tenantId);
// 根據方案決定要建立的表格
const baseTables = [
'users', 'members', 'check_ins', 'member_ships',
'payments', 'services', 'staff', 'roles'
];
const premiumTables = plan === 'premium' || plan === 'enterprise'
? ['analytics', 'reports', 'integrations', 'custom_fields']
: [];
const tables = [...baseTables, ...premiumTables];
for (const table of tables) {
const schema = await this.getTableSchema(table, plan);
await pool.query(schema);
}
// 建立索引和約束
await this.createIndexes(pool, plan);
await this.createConstraints(pool, plan);
}
private async seedDefaultData(tenantId: string, settings: TenantSettings): Promise<void> {
const pool = await this.connectionManager.getConnection(tenantId);
// 建立預設管理員使用者
await pool.query(`
INSERT INTO users (id, email, name, role, is_active, created_at)
VALUES ($1, $2, $3, $4, $5, $6)
`, [
`admin-${tenantId}`,
settings.adminEmail,
settings.adminName,
'admin',
true,
new Date()
]);
// 建立預設服務項目
const defaultServices = [
{ name: '健身房使用', price: 1500, duration: 30 },
{ name: '個人教練', price: 3000, duration: 60 },
{ name: '團體課程', price: 800, duration: 45 },
];
for (const service of defaultServices) {
await pool.query(`
INSERT INTO services (id, name, price, duration_minutes, is_active, created_at)
VALUES ($1, $2, $3, $4, $5, $6)
`, [
`service-${Date.now()}-${Math.random()}`,
service.name,
service.price,
service.duration,
true,
new Date()
]);
}
}
}
我們實作了完整的 RBAC (Role-Based Access Control) 系統，支援七種使用者角色：
// packages/kyo-core/src/auth/auth-service.ts
export class AuthService {
private readonly jwtSecret: string;
private readonly tokenExpiry = '24h';
private readonly refreshExpiry = '7d';
constructor(jwtSecret?: string) {
this.jwtSecret = jwtSecret || process.env.JWT_SECRET || 'dev-secret-key';
}
async authenticateUser(tenantId: string, credentials: LoginCredentials): Promise<AuthResult> {
const pool = await tenantConnectionManager.getConnection(tenantId);
// 驗證使用者憑證
const user = await this.validateCredentials(pool, credentials);
if (!user) {
throw new KyoError('E_AUTH_INVALID_CREDENTIALS', 'Invalid credentials', 401);
}
// 檢查使用者狀態
if (!user.isActive) {
throw new KyoError('E_AUTH_USER_INACTIVE', 'User account is inactive', 403);
}
// 產生 Token
const tokens = await this.generateTokens(user, tenantId);
// 記錄登入
await this.logUserActivity(pool, user.id, 'login', {
ip: credentials.ip,
userAgent: credentials.userAgent,
});
return {
user: this.sanitizeUser(user),
accessToken: tokens.accessToken,
refreshToken: tokens.refreshToken,
expiresIn: 24 * 60 * 60, // 24 hours in seconds
};
}
async generateTokens(user: User, tenantId: string): Promise<TokenPair> {
const payload: JWTPayload = {
userId: user.id,
tenantId,
role: user.role,
permissions: this.getRolePermissions(user.role),
iat: Math.floor(Date.now() / 1000),
};
const accessToken = jwt.sign(payload, this.jwtSecret, {
expiresIn: this.tokenExpiry,
issuer: 'kyo-system',
audience: tenantId,
});
const refreshToken = jwt.sign(
{ userId: user.id, tenantId, type: 'refresh' },
this.jwtSecret,
{ expiresIn: this.refreshExpiry }
);
return { accessToken, refreshToken };
}
private getRolePermissions(role: UserRole): Permission[] {
const rolePermissions: Record<UserRole, Permission[]> = {
admin: ['*'], // 全部權限
manager: [
'members:read', 'members:write', 'members:delete',
'staff:read', 'staff:write',
'services:read', 'services:write',
'reports:read', 'reports:export',
'payments:read', 'payments:write',
],
staff: [
'members:read', 'members:write',
'checkins:read', 'checkins:write',
'services:read',
'payments:read',
],
trainer: [
'members:read',
'checkins:read', 'checkins:write',
'services:read',
'training:read', 'training:write',
],
receptionist: [
'members:read', 'members:write',
'checkins:read', 'checkins:write',
'services:read',
'payments:read', 'payments:write',
],
accountant: [
'payments:read', 'payments:write', 'payments:export',
'reports:read', 'reports:export',
'members:read',
],
member: [
'profile:read', 'profile:write',
'checkins:read',
'services:read',
'payments:read',
],
};
return rolePermissions[role] || [];
}
async verifyToken(token: string, tenantId?: string): Promise<JWTPayload> {
try {
const payload = jwt.verify(token, this.jwtSecret, {
issuer: 'kyo-system',
audience: tenantId,
}) as JWTPayload;
// 檢查 Token 是否被撤銷（可選的黑名單機制）
await this.checkTokenBlacklist(payload);
return payload;
} catch (error) {
if (error instanceof jwt.TokenExpiredError) {
throw new KyoError('E_AUTH_TOKEN_EXPIRED', 'Token has expired', 401);
} else if (error instanceof jwt.JsonWebTokenError) {
throw new KyoError('E_AUTH_TOKEN_INVALID', 'Invalid token', 401);
}
throw error;
}
}
}
會員管理是健身房 SaaS 的核心功能，我們實作了完整的 CRUD 操作和進階查詢：
// packages/kyo-core/src/services/member-service.ts
export class MemberService {
constructor(private connectionManager: TenantConnectionManager) {}
async searchMembers(tenantId: string, params: MemberSearchParams): Promise<MemberSearchResult> {
const pool = await this.connectionManager.getConnection(tenantId);
// 建構查詢條件
const conditions: string[] = [];
const values: any[] = [];
let paramIndex = 1;
if (params.keyword) {
conditions.push(`(
m.name ILIKE $${paramIndex} OR
m.member_code ILIKE $${paramIndex} OR
m.email ILIKE $${paramIndex} OR
m.phone ILIKE $${paramIndex}
)`);
values.push(`%${params.keyword}%`);
paramIndex++;
}
if (params.status) {
conditions.push(`m.status = $${paramIndex}`);
values.push(params.status);
paramIndex++;
}
if (params.joinDateFrom) {
conditions.push(`m.join_date >= $${paramIndex}`);
values.push(params.joinDateFrom);
paramIndex++;
}
if (params.joinDateTo) {
conditions.push(`m.join_date <= $${paramIndex}`);
values.push(params.joinDateTo);
paramIndex++;
}
// 建構完整查詢
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
const orderBy = `ORDER BY ${params.sortBy || 'm.created_at'} ${params.sortOrder || 'DESC'}`;
const limit = params.limit || 20;
const offset = ((params.page || 1) - 1) * limit;
// 執行查詢
const query = `
SELECT
m.*,
ms.plan,
ms.status as membership_status,
ms.start_date as membership_start,
ms.end_date as membership_end,
(
SELECT COUNT(*)
FROM check_ins ci
WHERE ci.member_id = m.id
AND ci.check_in_time >= NOW() - INTERVAL '30 days'
) as visits_last_30_days,
(
SELECT ci.check_in_time
FROM check_ins ci
WHERE ci.member_id = m.id
ORDER BY ci.check_in_time DESC
LIMIT 1
) as last_visit
FROM members m
LEFT JOIN memberships ms ON m.id = ms.member_id AND ms.is_active = true
${whereClause}
${orderBy}
LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
`;
values.push(limit, offset);
const result = await pool.query(query, values);
// 取得總數
const countQuery = `
SELECT COUNT(*) as total
FROM members m
LEFT JOIN memberships ms ON m.id = ms.member_id AND ms.is_active = true
${whereClause}
`;
const countResult = await pool.query(countQuery, values.slice(0, -2));
const totalCount = parseInt(countResult.rows[0].total);
return {
members: result.rows.map(row => this.mapRowToMember(row)),
pagination: {
currentPage: params.page || 1,
totalPages: Math.ceil(totalCount / limit),
totalCount,
pageSize: limit,
},
};
}
async checkInMember(tenantId: string, identifier: string): Promise<CheckInResult> {
const pool = await this.connectionManager.getConnection(tenantId);
try {
await pool.query('BEGIN');
// 尋找會員
const member = await this.findMemberByIdentifier(pool, identifier);
if (!member) {
throw new KyoError('E_MEMBER_NOT_FOUND', 'Member not found', 404);
}
// 檢查會員狀態
if (member.membershipInfo.status !== 'active') {
throw new KyoError('E_MEMBERSHIP_INACTIVE', 'Membership is not active', 400);
}
// 檢查是否已經報到
const todayCheckin = await pool.query(`
SELECT id FROM check_ins
WHERE member_id = $1
AND DATE(check_in_time) = CURRENT_DATE
AND check_out_time IS NULL
`, [member.id]);
if (todayCheckin.rows.length > 0) {
throw new KyoError('E_ALREADY_CHECKED_IN', 'Member already checked in today', 400);
}
// 記錄報到
const checkInResult = await pool.query(`
INSERT INTO check_ins (id, member_id, check_in_time, created_at)
VALUES ($1, $2, $3, $4)
RETURNING *
`, [
`checkin-${Date.now()}-${Math.random()}`,
member.id,
new Date(),
new Date()
]);
// 更新會員最後到訪時間
await pool.query(`
UPDATE members
SET last_visit = $1, visit_count = visit_count + 1
WHERE id = $2
`, [new Date(), member.id]);
await pool.query('COMMIT');
return {
success: true,
member: member,
checkIn: this.mapRowToCheckIn(checkInResult.rows[0]),
message: '報到成功',
};
} catch (error) {
await pool.query('ROLLBACK');
throw error;
}
}
}
我們使用 Fastify 建立 RESTful API，支援租戶上下文和認證授權：
// apps/kyo-otp-service/src/routes/tenant-routes.ts
export async function tenantRoutes(fastify: FastifyInstance) {
// 租戶身份驗證中間件
fastify.addHook('preHandler', async (request) => {
const tenantId = request.headers['x-tenant-id'] as string;
if (!tenantId) {
throw new Error('Tenant ID is required');
}
request.tenantId = tenantId;
});
// 會員搜尋
fastify.get('/members', {
schema: {
querystring: MemberSearchParamsSchema,
response: { 200: MemberSearchResultSchema },
},
}, async (request) => {
const params = request.query as MemberSearchParams;
return await memberService.searchMembers(request.tenantId, params);
});
// 會員報到
fastify.post('/members/check-in', {
schema: {
body: Type.Object({
identifier: Type.String({ minLength: 1, description: '會員編號、手機或email' }),
}),
response: { 200: CheckInResultSchema },
},
}, async (request) => {
const { identifier } = request.body as { identifier: string };
return await memberService.checkInMember(request.tenantId, identifier);
});
// 更新會員狀態
fastify.patch('/members/:id/status', {
schema: {
params: Type.Object({ id: Type.String() }),
body: Type.Object({ status: Type.Enum(MemberStatus) }),
},
}, async (request) => {
const { id } = request.params as { id: string };
const { status } = request.body as { status: MemberStatus };
return await memberService.updateMemberStatus(request.tenantId, id, status);
});
}
// 連線監控
async getConnectionStats(): Promise<ConnectionStats[]> {
const stats: ConnectionStats[] = [];
for (const [tenantId, connection] of this.connections) {
stats.push({
tenantId,
activeConnections: connection.pool.totalCount,
idleConnections: connection.pool.idleCount,
waitingClients: connection.pool.waitingCount,
lastUsed: connection.lastUsed,
isHealthy: connection.isActive,
});
}
return stats;
}
// 效能監控
private async logSlowQuery(query: string, duration: number, tenantId: string) {
if (duration > 1000) { // 超過1秒的查詢
console.warn('Slow query detected:', {
tenantId,
query: query.substring(0, 100),
duration,
timestamp: new Date(),
});
}
}
我們今天實作了完整的後端多租戶架構：
iThome鐵人賽
看影片追技術